Dashboard creation
Undercurrent uses Grafana for analytics dashboards. You get your own Grafana organization with a pre-configured SQL data source connected to your events. You can build dashboards by hand, but this work is well suited to an AI coding harness.
Delegating to an AI harness
Paste this prompt into your coding harness in your app repo. It reads your app’s analytics events from the source code, designs dashboards, and creates them over the Grafana HTTP API, using the credentials contained in your credentials file.
Undercurrent Analytics is a mobile analytics service that uses Grafana for analytics dashboards. There is an Undercurrent Analytics credentials JSON file in this repo, containing a Grafana Service Account token with Editor permissions, and the login/password credentials for a Grafana org admin, which you can use to interact with Grafana over the HTTP API.
Your task is to create a new Grafana dashboard using the HTTP API, which shows several different panels containing valuable user analytics metrics for this app.
Follow this step-by-step process:
1. Find the Undercurrent Analytics credentials JSON file in this repo. If you can't find it, stop here and report that you need it to create the dashboards. If you do find it, ensure it is ignored by version control and not checked in.
2. Find the existing R2 SQL data source in the Grafana instance: https://grafana.undercurrentanalytics.dev. If you can't find it, stop here and report what went wrong.
3. Verify that the data source works by running the query `SELECT * FROM $__table LIMIT 1`. If it breaks, stop here and report the error and what went wrong.
4. Look through the source code to find the events that the app tracks. If you can't find any, then stop here, because we need to register analytics events to track before we can create dashboards.
5. Based on the purpose of the app and its intended user base, design the dashboards you will create along with the set of panels in each dashboard. Prefer to make fewer dashboards if possible - ideally just a single one. These are the most important areas to cover when choosing the metrics to visualize: (1) Activation: Are new users reaching the point at which the app becomes useful to them? (2) Engagement: Which features and screens are used, and how often? Are users benefitting from the app's key features? (3) Retention: Are users coming back over time? Which features do they come back to?
6. Using an edited version of the `build_dashboard.py` Python snippet mentioned in the notes below, create the dashboard(s) and panels you designed in the previous step. Don't add `build_dashboard.py` to version control - either ignore it, or put it somewhere that is already not tracked by version control. The same goes for any other Python-related files that are now present in the repo, such as `__pycache__/`.
7. Smoke test the dashboards and panels against the live data source.
8. Once you're finished, list the dashboards with their Grafana URLs, and the panels you created, the question each one answers, and how they're relevant for growing the app's usage.
Here are some essential notes required to complete this process successfully:
# Writing SQL queries for Undercurrent Analytics
## The tracking events table: `$__table`
During query execution, `$__table` is expanded into a view that is already filtered to show only your data, so:
- In queries, write `FROM $__table` (optionally followed by WHERE/GROUP BY/ORDER BY/HAVING/
LIMIT/OFFSET/JOIN…ON).
- Do NOT alias it (no `$__table t`); reference its columns unqualified.
## The time filter
Include `WHERE $__timeFilter(time)` in queries when reading from $__table, so you are only reading events within the selected time range.
## The table schema ($__table columns)
| Column | Type | Non-null | Notes |
|-----------------------------|----------------|----------|--------------------------------------------------------|
| `event` | string | ✅ | Event name |
| `time` | timestamp (ms) | ✅ | Event time |
| `distinct_id` | string | | Identifies a unique installation of the app |
| `properties` | string | | JSON string of custom properties attached to the event |
| `app_build_number` | string | | The app's build number |
| `app_version_string` | string | | Mixpanel `$app_version_string` |
| `carrier` | string | | Mixpanel `$carrier` |
| `city` | string | | Mixpanel `$city` |
| `device_id` | string | | Mixpanel `$device_id` |
| `had_persisted_distinct_id` | bool | | Mixpanel `$had_persisted_distinct_id` |
| `lib_version` | string | | Mixpanel `$lib_version` |
| `manufacturer` | string | | Mixpanel `$manufacturer` |
| `model` | string | | Mixpanel `$model` |
| `os` | string | | Mixpanel `$os` |
| `os_version` | string | | Mixpanel `$os_version` |
| `radio` | string | | Mixpanel `$radio` |
| `region` | string | | Mixpanel `$region` |
| `screen_height` | int64 | | Mixpanel `$screen_height` |
| `screen_width` | int64 | | Mixpanel `$screen_width` |
| `user_id` | string | | Mixpanel `$user_id` |
| `wifi` | bool | | Mixpanel `$wifi` |
| `mp_country_code` | string | | Mixpanel `mp_country_code` |
| `mp_lib` | string | | Mixpanel `mp_lib` |
| `mp_processing_time_ms` | int64 | | Mixpanel `mp_processing_time_ms` |
| `mp_event_size` | int64 | | Mixpanel `$mp_event_size` |
(There are other columns, but they are irrelevant, so never use them.)
## SQL dialect
Uses Cloudflare R2 SQL. See: https://developers.cloudflare.com/r2-sql/sql-reference/, but note that you must only use the table '$__table', and you cannot run SHOW or DESCRIBE statements.
- SELECT statements only.
- Use WITH to join and self-reference the events table.
- Don't use WITH RECURSIVE.
## Example: An "events per hour" timeseries panel's SQL query
```sql
SELECT date_trunc('hour', time) AS hour, COUNT(*) AS event_count
FROM $__table
WHERE $__timeFilter(time)
GROUP BY date_trunc('hour', time)
ORDER BY date_trunc('hour', time)
```
# `build_dashboard.py` Python script
```python
#!/usr/bin/env python3
"""Deploy a Grafana dashboard via the HTTP API.
Usage:
python3 build_dashboard.py smoke # validate each panel query against the datasource
python3 build_dashboard.py deploy # smoke-test, then create/update the dashboard
"""
import json, sys, time, urllib.request, urllib.error
# ── Configuration ──────────────────────────────────────────────────────────────
GRAFANA_URL = "https://grafana.undercurrentanalytics.dev"
API_KEY = "<your Grafana API key here>"
DATASOURCE = {"type": "agentified-r2sql-datasource", "uid": "<datasource UID here>"}
DASHBOARD_UID = "<dashboard UID if exists>" # existing dashboard; update in place
DASHBOARD_TITLE = "<dashboard title>"
DASHBOARD_TAGS = [] # List of string tags
# Appended to every $__timeFilter(time) clause. Set to `None` to disable.
EXTRA_FILTER = None
# ── App-specific SQL fragments ─────────────────────────────────────────────────
# ...
# ── Panels ─────────────────────────────────────────────────────────────────────
# Each entry: (title, viz_type, rawSql, fieldConfig_overrides, options, w, h)
# viz_type – Grafana panel type id, e.g. "timeseries", "stat", "barchart", "piechart", "table"
# fieldConfig_overrides – merged into defaults; use None for no overrides
# options – panel options dict; use None for defaults
# w, h – grid units (grid is 24 wide)
# Two examples included below. Replace them with the dashboards for this app.
PANELS = [
("Daily active users", "timeseries",
"SELECT date_trunc('day', time) AS day, COUNT(DISTINCT distinct_id) AS dau "
"FROM $__table WHERE event='app_launch' AND $__timeFilter(time) "
"GROUP BY date_trunc('day', time) ORDER BY day",
{"custom": {"drawStyle": "line", "lineWidth": 2, "fillOpacity": 10,
"pointSize": 6, "showPoints": "always"}, "unit": "short"}, None, 16, 8),
("Event volume per day", "timeseries",
"SELECT date_trunc('day', time) AS day, COUNT(*) AS events FROM $__table "
"WHERE $__timeFilter(time) GROUP BY date_trunc('day', time) ORDER BY day",
{"custom": {"drawStyle": "bars", "fillOpacity": 70, "lineWidth": 1},
"unit": "short"}, None, 12, 8),
]
# ── Infrastructure ─────────────────────────────────────────────────────────────
def _inject_filter(sql):
if EXTRA_FILTER:
return sql.replace("$__timeFilter(time)", f"$__timeFilter(time) AND {EXTRA_FILTER}")
return sql
def http(method, path, body=None):
data = json.dumps(body).encode() if body is not None else None
req = urllib.request.Request(
GRAFANA_URL + path, data=data, method=method,
headers={"Authorization": f"Bearer {API_KEY}",
"Content-Type": "application/json",
"User-Agent": "curl/8.4.0"})
def parse(raw):
try:
return json.loads(raw or "{}")
except json.JSONDecodeError:
return {"_raw": raw}
try:
with urllib.request.urlopen(req, timeout=60) as r:
return r.status, parse(r.read().decode())
except urllib.error.HTTPError as e:
return e.code, parse(e.read().decode())
def smoke():
now = int(time.time() * 1000)
frm = now - 400 * 86400 * 1000
ok = True
for title, _vt, sql, *_ in PANELS:
body = {"from": str(frm), "to": str(now), "queries": [
{"refId": "A", "datasource": DATASOURCE,
"rawSql": _inject_filter(sql), "format": "table"}]}
status, resp = http("POST", "/api/ds/query", body)
r = resp.get("results", {}).get("A", {})
st = r.get("status", status)
if st == 200:
frames = r.get("frames", [])
vals = frames[0]["data"]["values"] if frames and frames[0].get("data") else []
print(f" OK [{len(vals)}c x {len(vals[0]) if vals else 0}r] {title}")
else:
ok = False
print(f" FAIL [{st}] {title}\n {str(r.get('error') or resp)[:200]}")
return ok
def build_model():
panels = []
x = y = row_h = 0
for i, (title, vt, sql, fc, opts, w, h) in enumerate(PANELS, start=1):
if x + w > 24:
x = 0
y += row_h
row_h = 0
defaults = {"color": {"mode": "palette-classic"}, "custom": {}, "unit": "short"}
for k, v in (fc or {}).items():
if isinstance(v, dict) and isinstance(defaults.get(k), dict):
defaults[k] = {**defaults[k], **v}
else:
defaults[k] = v
panel = {
"datasource": DATASOURCE,
"fieldConfig": {"defaults": defaults, "overrides": []},
"gridPos": {"h": h, "w": w, "x": x, "y": y},
"id": i,
"title": title,
"type": vt,
"targets": [{"datasource": DATASOURCE, "rawSql": _inject_filter(sql),
"refId": "A", "format": "table"}],
}
if opts:
panel["options"] = opts
panels.append(panel)
x += w
row_h = max(row_h, h)
return {
"annotations": {"list": []},
"editable": True,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"links": [],
"panels": panels,
"refresh": "",
"schemaVersion": 39,
"tags": DASHBOARD_TAGS,
"templating": {"list": []},
"time": {"from": "now-30d", "to": "now"},
"timepicker": {},
"timezone": "",
"title": DASHBOARD_TITLE,
"uid": DASHBOARD_UID,
"weekStart": "",
}
def deploy():
print("Smoke-testing panel queries...")
if not smoke():
print("\nAborting deploy: one or more panel queries failed.")
sys.exit(1)
print("\nAll queries OK. Creating dashboard...")
status, resp = http("POST", "/api/dashboards/db",
{"dashboard": build_model(), "overwrite": True})
print(status, json.dumps(resp, indent=2))
if resp.get("url"):
print(f"\nOpen: {GRAFANA_URL}{resp['url']}")
if __name__ == "__main__":
cmd = sys.argv[1] if len(sys.argv) > 1 else "smoke"
if cmd == "smoke":
smoke()
elif cmd == "deploy":
deploy()
else:
print(__doc__)
```
What it covers
The prompt directs your AI harness to design panels around the three groups of metrics that matter most for a growing app:
- Activation: Are new users reaching the point where the app becomes useful?
- Engagement: Which features and screens are used, and how often?
- Retention: Are users coming back over time, and to which features?
It prefers a small number of dashboards (ideally one), smoke-tests every panel query against your live data, and then it should give you links to the dashboards it created.
The panel queries are written in SQL against your events table. See SQL queries for the schema and dialect.
Viewing and editing dashboards
Go to grafana.undercurrentanalytics.dev and sign in with the username and password from your credentials file. From there you can browse the dashboards, tweak panels, or build new ones directly. You can always ask your AI coding harness to edit the dashboards or create new ones by including the following prompt fragment:
Undercurrent Analytics is a mobile analytics service that uses Grafana for analytics dashboards. There is an Undercurrent Analytics credentials JSON file in this repo, containing a Grafana Service Account token with Editor permissions, and the login/password credentials for a Grafana org admin, which you can use to interact with Grafana over the HTTP API.
Here are some very useful notes for creating and editing Grafana dashboards for Undercurrent Analytics:
# Finding the app's registered analytics events
Look through the source code to find the events that the app tracks. If you can't find any, then stop and let me know, because we need to register analytics events to track before we can create dashboards.
# Writing SQL queries for Undercurrent Analytics
## The tracking events table: `$__table`
During query execution, `$__table` is expanded into a view that is already filtered to show only your data, so:
- In queries, write `FROM $__table` (optionally followed by WHERE/GROUP BY/ORDER BY/HAVING/
LIMIT/OFFSET/JOIN…ON).
- Do NOT alias it (no `$__table t`); reference its columns unqualified.
## The time filter
Include `WHERE $__timeFilter(time)` in queries when reading from $__table, so you are only reading events within the selected time range.
## The table schema ($__table columns)
| Column | Type | Non-null | Notes |
|-----------------------------|----------------|----------|--------------------------------------------------------|
| `event` | string | ✅ | Event name |
| `time` | timestamp (ms) | ✅ | Event time |
| `distinct_id` | string | | Identifies a unique installation of the app |
| `properties` | string | | JSON string of custom properties attached to the event |
| `app_build_number` | string | | The app's build number |
| `app_version_string` | string | | Mixpanel `$app_version_string` |
| `carrier` | string | | Mixpanel `$carrier` |
| `city` | string | | Mixpanel `$city` |
| `device_id` | string | | Mixpanel `$device_id` |
| `had_persisted_distinct_id` | bool | | Mixpanel `$had_persisted_distinct_id` |
| `lib_version` | string | | Mixpanel `$lib_version` |
| `manufacturer` | string | | Mixpanel `$manufacturer` |
| `model` | string | | Mixpanel `$model` |
| `os` | string | | Mixpanel `$os` |
| `os_version` | string | | Mixpanel `$os_version` |
| `radio` | string | | Mixpanel `$radio` |
| `region` | string | | Mixpanel `$region` |
| `screen_height` | int64 | | Mixpanel `$screen_height` |
| `screen_width` | int64 | | Mixpanel `$screen_width` |
| `user_id` | string | | Mixpanel `$user_id` |
| `wifi` | bool | | Mixpanel `$wifi` |
| `mp_country_code` | string | | Mixpanel `mp_country_code` |
| `mp_lib` | string | | Mixpanel `mp_lib` |
| `mp_processing_time_ms` | int64 | | Mixpanel `mp_processing_time_ms` |
| `mp_event_size` | int64 | | Mixpanel `$mp_event_size` |
(There are other columns, but they are irrelevant, so never use them.)
## SQL dialect
Uses Cloudflare R2 SQL. See: https://developers.cloudflare.com/r2-sql/sql-reference/, but note that you must only use the table '$__table', and you cannot run SHOW or DESCRIBE statements.
- SELECT statements only.
- Use WITH to join and self-reference the events table.
- Don't use WITH RECURSIVE.
## Example: An "events per hour" timeseries panel's SQL query
```sql
SELECT date_trunc('hour', time) AS hour, COUNT(*) AS event_count
FROM $__table
WHERE $__timeFilter(time)
GROUP BY date_trunc('hour', time)
ORDER BY date_trunc('hour', time)
```
# Creating Grafana dashboards via the HTTP API
## Authentication
Find the Undercurrent Analytics credentials JSON file in this repo. If you can't find it, stop here and report that you need it to create the dashboards. If you do find it, ensure it is ignored by version control and not checked in.
## Using the HTTP API
You can use an edited version of the below `build_dashboard.py` Python snippet to create the dashboard(s) and panels. Don't add `build_dashboard.py` to version control - either ignore it, or put it somewhere that is already not tracked by version control. The same goes for any other Python-related files that are now present in the repo, such as `__pycache__/`.
Smoke test the dashboards and panels against the live data source after you've created them to ensure that they really work. This is important.
### Appendix: `build_dashboard.py` Python script
```python
#!/usr/bin/env python3
"""Deploy a Grafana dashboard via the HTTP API.
Usage:
python3 build_dashboard.py smoke # validate each panel query against the datasource
python3 build_dashboard.py deploy # smoke-test, then create/update the dashboard
"""
import json, sys, time, urllib.request, urllib.error
# ── Configuration ──────────────────────────────────────────────────────────────
GRAFANA_URL = "https://grafana.undercurrentanalytics.dev"
API_KEY = "<your Grafana API key here>"
DATASOURCE = {"type": "agentified-r2sql-datasource", "uid": "<datasource UID here>"}
DASHBOARD_UID = "<dashboard UID if exists>" # existing dashboard; update in place
DASHBOARD_TITLE = "<dashboard title>"
DASHBOARD_TAGS = [] # List of string tags
# Appended to every $__timeFilter(time) clause. Set to `None` to disable.
EXTRA_FILTER = None
# ── App-specific SQL fragments ─────────────────────────────────────────────────
# ...
# ── Panels ─────────────────────────────────────────────────────────────────────
# Each entry: (title, viz_type, rawSql, fieldConfig_overrides, options, w, h)
# viz_type – Grafana panel type id, e.g. "timeseries", "stat", "barchart", "piechart", "table"
# fieldConfig_overrides – merged into defaults; use None for no overrides
# options – panel options dict; use None for defaults
# w, h – grid units (grid is 24 wide)
# Two examples included below. Replace them with the dashboards for this app.
PANELS = [
("Daily active users", "timeseries",
"SELECT date_trunc('day', time) AS day, COUNT(DISTINCT distinct_id) AS dau "
"FROM $__table WHERE event='app_launch' AND $__timeFilter(time) "
"GROUP BY date_trunc('day', time) ORDER BY day",
{"custom": {"drawStyle": "line", "lineWidth": 2, "fillOpacity": 10,
"pointSize": 6, "showPoints": "always"}, "unit": "short"}, None, 16, 8),
("Event volume per day", "timeseries",
"SELECT date_trunc('day', time) AS day, COUNT(*) AS events FROM $__table "
"WHERE $__timeFilter(time) GROUP BY date_trunc('day', time) ORDER BY day",
{"custom": {"drawStyle": "bars", "fillOpacity": 70, "lineWidth": 1},
"unit": "short"}, None, 12, 8),
]
# ── Infrastructure ─────────────────────────────────────────────────────────────
def _inject_filter(sql):
if EXTRA_FILTER:
return sql.replace("$__timeFilter(time)", f"$__timeFilter(time) AND {EXTRA_FILTER}")
return sql
def http(method, path, body=None):
data = json.dumps(body).encode() if body is not None else None
req = urllib.request.Request(
GRAFANA_URL + path, data=data, method=method,
headers={"Authorization": f"Bearer {API_KEY}",
"Content-Type": "application/json",
"User-Agent": "curl/8.4.0"})
def parse(raw):
try:
return json.loads(raw or "{}")
except json.JSONDecodeError:
return {"_raw": raw}
try:
with urllib.request.urlopen(req, timeout=60) as r:
return r.status, parse(r.read().decode())
except urllib.error.HTTPError as e:
return e.code, parse(e.read().decode())
def smoke():
now = int(time.time() * 1000)
frm = now - 400 * 86400 * 1000
ok = True
for title, _vt, sql, *_ in PANELS:
body = {"from": str(frm), "to": str(now), "queries": [
{"refId": "A", "datasource": DATASOURCE,
"rawSql": _inject_filter(sql), "format": "table"}]}
status, resp = http("POST", "/api/ds/query", body)
r = resp.get("results", {}).get("A", {})
st = r.get("status", status)
if st == 200:
frames = r.get("frames", [])
vals = frames[0]["data"]["values"] if frames and frames[0].get("data") else []
print(f" OK [{len(vals)}c x {len(vals[0]) if vals else 0}r] {title}")
else:
ok = False
print(f" FAIL [{st}] {title}\n {str(r.get('error') or resp)[:200]}")
return ok
def build_model():
panels = []
x = y = row_h = 0
for i, (title, vt, sql, fc, opts, w, h) in enumerate(PANELS, start=1):
if x + w > 24:
x = 0
y += row_h
row_h = 0
defaults = {"color": {"mode": "palette-classic"}, "custom": {}, "unit": "short"}
for k, v in (fc or {}).items():
if isinstance(v, dict) and isinstance(defaults.get(k), dict):
defaults[k] = {**defaults[k], **v}
else:
defaults[k] = v
panel = {
"datasource": DATASOURCE,
"fieldConfig": {"defaults": defaults, "overrides": []},
"gridPos": {"h": h, "w": w, "x": x, "y": y},
"id": i,
"title": title,
"type": vt,
"targets": [{"datasource": DATASOURCE, "rawSql": _inject_filter(sql),
"refId": "A", "format": "table"}],
}
if opts:
panel["options"] = opts
panels.append(panel)
x += w
row_h = max(row_h, h)
return {
"annotations": {"list": []},
"editable": True,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"links": [],
"panels": panels,
"refresh": "",
"schemaVersion": 39,
"tags": DASHBOARD_TAGS,
"templating": {"list": []},
"time": {"from": "now-30d", "to": "now"},
"timepicker": {},
"timezone": "",
"title": DASHBOARD_TITLE,
"uid": DASHBOARD_UID,
"weekStart": "",
}
def deploy():
print("Smoke-testing panel queries...")
if not smoke():
print("\nAborting deploy: one or more panel queries failed.")
sys.exit(1)
print("\nAll queries OK. Creating dashboard...")
status, resp = http("POST", "/api/dashboards/db",
{"dashboard": build_model(), "overwrite": True})
print(status, json.dumps(resp, indent=2))
if resp.get("url"):
print(f"\nOpen: {GRAFANA_URL}{resp['url']}")
if __name__ == "__main__":
cmd = sys.argv[1] if len(sys.argv) > 1 else "smoke"
if cmd == "smoke":
smoke()
elif cmd == "deploy":
deploy()
else:
print(__doc__)
```
## Reporting back
Once you're finished, give me the links to any dashboards you created or edited.